package com.pethospital.dao.impl;

import com.pethospital.dao.HealthRecordDao;
import com.pethospital.entity.HealthRecord;
import com.pethospital.entity.PageResult;
import com.pethospital.entity.Pet;
import com.pethospital.util.DBUtil;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

/**
 * 健康记录数据访问实现类
 * Health Record Data Access Object Implementation
 * 
 * @author PetHospital System
 * @version 1.0
 */
public class HealthRecordDaoImpl implements HealthRecordDao {
    
    @Override
    public HealthRecord findById(Integer recordId) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.record_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, recordId);
            rs = pstmt.executeQuery();
            
            if (rs.next()) {
                return mapResultSetToHealthRecord(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return null;
    }
    
    @Override
    public List<HealthRecord> findByPetId(Integer petId) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.pet_id = ? ORDER BY hr.visit_date DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return healthRecords;
    }
    
    @Override
    public List<HealthRecord> findByOwnerId(Integer ownerId) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE p.owner_id = ? ORDER BY hr.visit_date DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return healthRecords;
    }
    
    @Override
    public int insert(HealthRecord healthRecord) {
        String sql = "INSERT INTO health_records (pet_id, visit_date, symptoms, diagnosis, " +
                    "treatment, medication, doctor_name, next_visit_date, cost, notes) " +
                    "VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, healthRecord.getPetId());
            pstmt.setDate(2, healthRecord.getVisitDate());
            pstmt.setString(3, healthRecord.getSymptoms());
            pstmt.setString(4, healthRecord.getDiagnosis());
            pstmt.setString(5, healthRecord.getTreatment());
            pstmt.setString(6, healthRecord.getMedication());
            pstmt.setString(7, healthRecord.getDoctorName());
            pstmt.setDate(8, healthRecord.getNextVisitDate());
            pstmt.setBigDecimal(9, healthRecord.getCost());
            pstmt.setString(10, healthRecord.getNotes());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int update(HealthRecord healthRecord) {
        String sql = "UPDATE health_records SET pet_id = ?, visit_date = ?, symptoms = ?, " +
                    "diagnosis = ?, treatment = ?, medication = ?, doctor_name = ?, " +
                    "next_visit_date = ?, cost = ?, notes = ?, update_time = CURRENT_TIMESTAMP " +
                    "WHERE record_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, healthRecord.getPetId());
            pstmt.setDate(2, healthRecord.getVisitDate());
            pstmt.setString(3, healthRecord.getSymptoms());
            pstmt.setString(4, healthRecord.getDiagnosis());
            pstmt.setString(5, healthRecord.getTreatment());
            pstmt.setString(6, healthRecord.getMedication());
            pstmt.setString(7, healthRecord.getDoctorName());
            pstmt.setDate(8, healthRecord.getNextVisitDate());
            pstmt.setBigDecimal(9, healthRecord.getCost());
            pstmt.setString(10, healthRecord.getNotes());
            pstmt.setInt(11, healthRecord.getRecordId());
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int delete(Integer recordId) {
        String sql = "DELETE FROM health_records WHERE record_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, recordId);
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public int batchDelete(Integer[] recordIds) {
        if (recordIds == null || recordIds.length == 0) {
            return 0;
        }
        
        StringBuilder sql = new StringBuilder("DELETE FROM health_records WHERE record_id IN (");
        for (int i = 0; i < recordIds.length; i++) {
            sql.append("?");
            if (i < recordIds.length - 1) {
                sql.append(",");
            }
        }
        sql.append(")");
        
        Connection conn = null;
        PreparedStatement pstmt = null;
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < recordIds.length; i++) {
                pstmt.setInt(i + 1, recordIds[i]);
            }
            
            return pstmt.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, null);
        }
        
        return 0;
    }
    
    @Override
    public List<HealthRecord> findAll() {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "ORDER BY hr.visit_date DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();
        
        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();
            
            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }
        
        return healthRecords;
    }
    
    @Override
    public PageResult<HealthRecord> findByPage(int currentPage, int pageSize) {
        // 计算偏移量
        int offset = (currentPage - 1) * pageSize;

        // 查询总记录数
        long totalCount = countAll();

        // 查询分页数据
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "ORDER BY hr.visit_date DESC LIMIT ? OFFSET ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, pageSize);
            pstmt.setInt(2, offset);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return new PageResult<>(healthRecords, currentPage, pageSize, totalCount);
    }

    @Override
    public PageResult<HealthRecord> findByPetIdAndPage(Integer petId, int currentPage, int pageSize) {
        // 计算偏移量
        int offset = (currentPage - 1) * pageSize;

        // 查询总记录数
        long totalCount = countByPetId(petId);

        // 查询分页数据
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.pet_id = ? ORDER BY hr.visit_date DESC LIMIT ? OFFSET ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, offset);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return new PageResult<>(healthRecords, currentPage, pageSize, totalCount);
    }

    @Override
    public PageResult<HealthRecord> findByOwnerIdAndPage(Integer ownerId, int currentPage, int pageSize) {
        // 计算偏移量
        int offset = (currentPage - 1) * pageSize;

        // 查询总记录数
        long totalCount = countByOwnerId(ownerId);

        // 查询分页数据
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE p.owner_id = ? ORDER BY hr.visit_date DESC LIMIT ? OFFSET ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            pstmt.setInt(2, pageSize);
            pstmt.setInt(3, offset);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return new PageResult<>(healthRecords, currentPage, pageSize, totalCount);
    }

    @Override
    public long countAll() {
        String sql = "SELECT COUNT(*) FROM health_records";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByPetId(Integer petId) {
        String sql = "SELECT COUNT(*) FROM health_records WHERE pet_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByOwnerId(Integer ownerId) {
        String sql = "SELECT COUNT(*) FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE p.owner_id = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, ownerId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    /**
     * 将ResultSet映射为HealthRecord对象
     */
    private HealthRecord mapResultSetToHealthRecord(ResultSet rs) throws SQLException {
        HealthRecord healthRecord = new HealthRecord();
        healthRecord.setRecordId(rs.getInt("record_id"));
        healthRecord.setPetId(rs.getInt("pet_id"));
        healthRecord.setVisitDate(rs.getDate("visit_date"));
        healthRecord.setSymptoms(rs.getString("symptoms"));
        healthRecord.setDiagnosis(rs.getString("diagnosis"));
        healthRecord.setTreatment(rs.getString("treatment"));
        healthRecord.setMedication(rs.getString("medication"));
        healthRecord.setDoctorName(rs.getString("doctor_name"));
        healthRecord.setNextVisitDate(rs.getDate("next_visit_date"));
        healthRecord.setCost(rs.getBigDecimal("cost"));
        healthRecord.setNotes(rs.getString("notes"));
        healthRecord.setCreateTime(rs.getTimestamp("create_time"));
        healthRecord.setUpdateTime(rs.getTimestamp("update_time"));

        // 设置关联的宠物信息
        Pet pet = new Pet();
        pet.setPetId(rs.getInt("pet_id"));
        pet.setPetName(rs.getString("pet_name"));
        pet.setPetType(rs.getString("pet_type"));
        pet.setBreed(rs.getString("breed"));
        healthRecord.setPet(pet);

        return healthRecord;
    }

    @Override
    public PageResult<HealthRecord> findByCondition(String petName, String diagnosis, String doctorName,
                                                   Date visitDateStart, Date visitDateEnd, Integer ownerId,
                                                   int currentPage, int pageSize) {
        // 构建查询条件
        StringBuilder sqlBuilder = new StringBuilder();
        sqlBuilder.append("SELECT hr.*, p.pet_name, p.pet_type, p.breed ");
        sqlBuilder.append("FROM health_records hr ");
        sqlBuilder.append("LEFT JOIN pets p ON hr.pet_id = p.pet_id ");
        sqlBuilder.append("WHERE 1=1 ");

        List<Object> params = new ArrayList<>();

        if (petName != null && !petName.trim().isEmpty()) {
            sqlBuilder.append("AND p.pet_name LIKE ? ");
            params.add("%" + petName.trim() + "%");
        }

        if (diagnosis != null && !diagnosis.trim().isEmpty()) {
            sqlBuilder.append("AND hr.diagnosis LIKE ? ");
            params.add("%" + diagnosis.trim() + "%");
        }

        if (doctorName != null && !doctorName.trim().isEmpty()) {
            sqlBuilder.append("AND hr.doctor_name = ? ");
            params.add(doctorName.trim());
        }

        if (visitDateStart != null) {
            sqlBuilder.append("AND hr.visit_date >= ? ");
            params.add(visitDateStart);
        }

        if (visitDateEnd != null) {
            sqlBuilder.append("AND hr.visit_date <= ? ");
            params.add(visitDateEnd);
        }

        if (ownerId != null) {
            sqlBuilder.append("AND p.owner_id = ? ");
            params.add(ownerId);
        }

        // 先查询总记录数
        String countSql = sqlBuilder.toString().replace("SELECT hr.*, p.pet_name, p.pet_type, p.breed", "SELECT COUNT(*)");
        long totalCount = executeCountQuery(countSql, params);

        // 添加排序和分页
        sqlBuilder.append("ORDER BY hr.visit_date DESC LIMIT ? OFFSET ?");
        params.add(pageSize);
        params.add((currentPage - 1) * pageSize);

        // 查询分页数据
        List<HealthRecord> healthRecords = executeQuery(sqlBuilder.toString(), params);

        return new PageResult<>(healthRecords, currentPage, pageSize, totalCount);
    }

    @Override
    public long countByDoctor(String doctorName) {
        String sql = "SELECT COUNT(*) FROM health_records WHERE doctor_name = ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, doctorName);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public long countByDateRange(Date startDate, Date endDate) {
        String sql = "SELECT COUNT(*) FROM health_records WHERE visit_date BETWEEN ? AND ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setDate(1, startDate);
            pstmt.setDate(2, endDate);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public List<Object[]> getDoctorStatistics() {
        String sql = "SELECT doctor_name, COUNT(*) as record_count " +
                    "FROM health_records " +
                    "WHERE doctor_name IS NOT NULL " +
                    "GROUP BY doctor_name " +
                    "ORDER BY record_count DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] row = new Object[2];
                row[0] = rs.getString("doctor_name");
                row[1] = rs.getLong("record_count");
                statistics.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    @Override
    public List<Object[]> getMonthlyStatistics(int year) {
        String sql = "SELECT MONTH(visit_date) as month, COUNT(*) as record_count " +
                    "FROM health_records " +
                    "WHERE YEAR(visit_date) = ? " +
                    "GROUP BY MONTH(visit_date) " +
                    "ORDER BY month";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<Object[]> statistics = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, year);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                Object[] row = new Object[2];
                row[0] = rs.getInt("month");
                row[1] = rs.getLong("record_count");
                statistics.add(row);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return statistics;
    }

    /**
     * 执行查询并返回结果列表
     */
    private List<HealthRecord> executeQuery(String sql, List<Object> params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return healthRecords;
    }

    /**
     * 执行计数查询
     */
    private long executeCountQuery(String sql, List<Object> params) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);

            // 设置参数
            for (int i = 0; i < params.size(); i++) {
                pstmt.setObject(i + 1, params.get(i));
            }

            rs = pstmt.executeQuery();

            if (rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return 0;
    }

    @Override
    public List<HealthRecord> findRecordsNeedingFollowUp() {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.next_visit_date IS NOT NULL " +
                    "AND hr.next_visit_date >= CURDATE() " +
                    "ORDER BY hr.next_visit_date ASC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return healthRecords;
    }

    @Override
    public List<HealthRecord> findRecentRecords(Integer petId, int limit) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.pet_id = ? " +
                    "ORDER BY hr.visit_date DESC LIMIT ?";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            pstmt.setInt(2, limit);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return healthRecords;
    }

    @Override
    public HealthRecord findLatestByPetId(Integer petId) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.pet_id = ? " +
                    "ORDER BY hr.visit_date DESC LIMIT 1";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, petId);
            rs = pstmt.executeQuery();

            if (rs.next()) {
                return mapResultSetToHealthRecord(rs);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return null;
    }

    @Override
    public List<HealthRecord> findTodayRecords() {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE DATE(hr.create_time) = CURDATE() " +
                    "ORDER BY hr.create_time DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return healthRecords;
    }

    @Override
    public List<HealthRecord> findByCostRange(Double minCost, Double maxCost) {
        String sql = "SELECT hr.*, p.pet_name, p.pet_type, p.breed " +
                    "FROM health_records hr " +
                    "LEFT JOIN pets p ON hr.pet_id = p.pet_id " +
                    "WHERE hr.cost BETWEEN ? AND ? " +
                    "ORDER BY hr.cost DESC";
        Connection conn = null;
        PreparedStatement pstmt = null;
        ResultSet rs = null;
        List<HealthRecord> healthRecords = new ArrayList<>();

        try {
            conn = DBUtil.getConnection();
            pstmt = conn.prepareStatement(sql);
            pstmt.setDouble(1, minCost);
            pstmt.setDouble(2, maxCost);
            rs = pstmt.executeQuery();

            while (rs.next()) {
                healthRecords.add(mapResultSetToHealthRecord(rs));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            DBUtil.closeAll(conn, pstmt, rs);
        }

        return healthRecords;
    }
}
